package wstrader.task.warehouse.weighing;

import java.sql.*;
import java.util.*;
import pc.system.*;
import pc.sql.value.*;

public class WN_IMEX extends WN
{
  //private String stype = null;
  //private String wn_table = null;
  //private String inst_table = null;

  public WN_IMEX(Tasks owner, char type)
  {
    super(owner,type);
    /*
    switch (type) {
        case WN_IMPORT :
          stype = "IM";
          inst_table = "delivery_instruction";
          break;
        case WN_EXPORT :
          stype = "EO";
          inst_table = "shipping_instruction";
          break;
    }
    wn_table = "WEIGHT_NOTE_" + stype;
}*/
  }
  public ResultSet getContract_List()
  {
    if (getType() == WN_IMPORT) return getContract_List('P');
    else return getContract_List(' ');
  }

  public ResultSet getBroker_List()
  {
    if (getType() == WN_IMPORT) return getBroker_List('P');
    else return getBroker_List(' ');
  }


  public ResultSet getInst_List()
  {
    int supplier_buyer_id = getRequest().getIntValue("supplier_buyer_id");
    String sql =
        "SELECT di.id, di.ref_number AS name, di.status " +
        "FROM " + getInst_Table() + " di " +
        "LEFT JOIN contracts ct ON di.contract_id = ct.id " +
        "WHERE (ct.supplier_buyer_id = ? OR ?) " +
        "ORDER BY name DESC";
    Vector vp = new Vector();
    vp.addElement(new IntValue(supplier_buyer_id));
    vp.addElement(new BooleanValue(supplier_buyer_id == 0));
    return getSQLCommand().executeQuery(sql,vp);
  }
/*
  public ResultSet getOrder_List()
  {
    int supplier_buyer_id = getRequest().getIntValue("supplier_buyer_id");
    String sql =
        "SELECT di.id, di.ref_number AS name, di.status " +
        "FROM " + order_table + " di " +
        "LEFT JOIN contracts ct ON di.contract_id = ct.id " +
        "WHERE (ct.supplier_buyer_id = ? OR ?) ";
    Vector vp = new Vector();
    vp.addElement(new IntValue(supplier_buyer_id));
    vp.addElement(new BooleanValue(supplier_buyer_id == 0));
    return getSQLCommand().executeQuery(sql,vp);
  }

*/
/*
  public ResultSet getBroker_List(char type)
  {
    int supplier_buyer_id = getRequest().getIntValue("supplier_buyer_id");
    String sql =
        "SELECT ct.id, ct.contract_no AS name " +
        "FROM contracts ct " +
        "WHERE (ct.type = ? OR ?) " +
        "  AND (ct.supplier_buyer_id = ? OR ?) " +
        "ORDER BY name DESC";

    Vector vp = new Vector();

    vp.addElement(new StringValue("" + type));
    vp.addElement(new BooleanValue(type == ' ' || type == 'A'));

    vp.addElement(new IntValue(supplier_buyer_id));
    vp.addElement(new BooleanValue(supplier_buyer_id == 0));
    return getSQLCommand().executeQuery(sql,vp);
  }
*/
  public ResultSet getWN_Detail()
  {
    int contract_id  = getRequest().getIntValue("contract_id");
    String broker_id = getRequest().getStringValue("broker_id","0");
    int inst_id      = getRequest().getIntValue("inst_id");
    int supplier_buyer_id = getRequest().getIntValue("supplier_buyer_id");
    int origin_id   = getRequest().getIntValue("origin_id");
    int quality_id  = getRequest().getIntValue("quality_id");
    int grade_id    = getRequest().getIntValue("grade_id");
    String sql =
        "SELECT ct.ref_number  as ct_ref_number, " +
        "       ct.contract_no," +
        "       di.ref_number as dsi_ref_number, " +
        "       qr.ref_number as qr_ref_number, " +
        //"       wr.ref_number as wr_ref_number, " +
        "       pk.name       as packing, " +
        "       su.name       as supplier_buyer, " +
        "       og.name       as origin, " +
        "       qu.name       as quality, " +
        "       gr.name       as grade, " +
        "       wn.* " +
        "FROM " + getWN_Table() + " wn " +
        "LEFT JOIN " + getInst_Table() + "   di ON wn.inst_id     = di.id " +
        "LEFT JOIN contracts            ct ON di.contract_id = ct.id " +
        //"LEFT JOIN warehouse_receipt    wr ON wn.wr_id       = wr.id " +
        "LEFT JOIN quality_report       qr ON wn.qr_id       = qr.id " +
        "LEFT JOIN packing_master       pk ON di.packing_id  = pk.id " +
        "LEFT JOIN company_master       su ON ct.supplier_buyer_id = su.id " +
        "LEFT JOIN origin_master        og ON ct.origin_id  = og.id " +
        "LEFT JOIN quality_master       qu ON ct.quality_id = qu.id " +
        "LEFT JOIN grade_master         gr ON ct.grade_id   = gr.id " +
        "WHERE (wn.inst_id           = ? OR ?) " +
        "  AND (di.contract_id       = ? OR ?) " +
        "  AND (ct.contract_no       = ? OR ?) " +
        "  AND (ct.supplier_buyer_id = ? OR ?) " +
        "  AND (ct.origin_id         = ? OR ?) " +
        "  AND (ct.quality_id        = ? OR ?) " +
        "  AND (ct.grade_id          = ? OR ?) " +
        "";
    Vector vp = new Vector();

    vp.addElement(new IntValue(inst_id));
    vp.addElement(new BooleanValue(inst_id == 0));

    vp.addElement(new IntValue(contract_id));
    vp.addElement(new BooleanValue(contract_id == 0));

    vp.addElement(new StringValue(broker_id));
    vp.addElement(new BooleanValue(broker_id.equals("0")));

    vp.addElement(new IntValue(supplier_buyer_id));
    vp.addElement(new BooleanValue(supplier_buyer_id == 0));

    vp.addElement(new IntValue(origin_id));
    vp.addElement(new BooleanValue(origin_id == 0));

    vp.addElement(new IntValue(quality_id));
    vp.addElement(new BooleanValue(quality_id == 0));

    vp.addElement(new IntValue(grade_id));
    vp.addElement(new BooleanValue(grade_id == 0));

    return getSQLCommand().executeQuery(sql,vp);
  }

  public ResultSet getWN_List()
  {
    int inst_id = getRequest().getIntValue("inst_id");
    String sql =
        "SELECT wn.id, wn.ref_number AS name, wn.status " +
        "FROM " + getWN_Table() + " wn " +
        "WHERE (wn.inst_id = ? OR ?) " +
        "ORDER BY status, name DESC";
    Vector vp = new Vector();
    vp.addElement(new IntValue(inst_id));
    vp.addElement(new BooleanValue(inst_id == 0));
    return getSQLCommand().executeQuery(sql,vp);
  }

  public Record getWN_Ref()
  {
    //long wn_id   = getRequest().getLongValue("wn_id");
    long inst_id = getRequest().getLongValue("inst_id");
    String sql = "SELECT ";
    if (id < 0) {
      sql +=
          "'" + getNew_WN() + "' AS ref_number, " +
          "      CURDATE() AS date, " +
          "      CURTIME() AS time, ";
    }
    sql +=
        "       ct.ref_number AS ct_ref_number," +
        "       ct.contract_no," +
        "       su.name AS supplier, " +
        "       og.name AS origin, " +
        "       qu.name AS quality, " +
        "       gr.name AS grade, " +
        "       pk.name AS packing, " +
        "       di.ref_number AS di_ref_number," +
        "       di.tons AS di_tons," +
        "       di.no_of_bags AS di_no_of_bags," +
        "       wn.* ";
    if (id <= 0) {
      sql +=
          "FROM " + getInst_Table() + " di " +
          "LEFT JOIN " + getWN_Table() + " wn  ON wn.id = 0 ";
    } else {
      sql +=
          "FROM " + getWN_Table() + " wn " +
          "LEFT JOIN " + getInst_Table() + " di ON wn.inst_id     = di.id ";
    }
    sql +=
        "LEFT JOIN contracts      ct ON di.contract_id       = ct.id " +
        "LEFT JOIN packing_master pk ON di.packing_id        = pk.id " +
        "LEFT JOIN company_master su ON ct.supplier_buyer_id = su.id " +
        "LEFT JOIN origin_master  og ON ct.origin_id         = og.id " +
        "LEFT JOIN quality_master qu ON ct.quality_id        = qu.id " +
        "LEFT JOIN grade_master   gr ON ct.grade_id          = gr.id ";
    if (id <= 0) {
      sql += "WHERE di.id = " + inst_id;
    }  else {
      sql += "WHERE wn.id = " + id;
    }
    return new Record(getSQLCommand().executeQuery(sql));
  }

/*
  public ResultSet getWNR_Detail()
  {
    String sql =
        "SELECT * FROM weight_note_ref WHERE wn_id = " + id;
    return getSQLCommand().executeQuery(sql);
  }
*/
    public void update_Inst(long inst_id)
    {
      //int inst_id = getRequest().getIntValue("inst_id");
      String sql =
          "SELECT SUM(no_of_bags)   AS delivered_bags, " +
          "       SUM(net_weight) AS delivered_tons " +
          "FROM " + getWN_Table() + " " +
          "WHERE inst_id = " + inst_id ;
      Record rc = new Record(getSQLCommand().executeQuery(sql));
      sql =
          "UPDATE " + getInst_Table() + " " +
          "SET delivered_bags = " +  rc.getIntValue("delivered_bags")   + ", " +
          "    delivered_tons = " +  rc.getFloatValue("delivered_tons") + " " +
          "WHERE id = " + inst_id;
      getSQLCommand().executeUpdate(sql);
      rc.close();
    }

    public void update_Contract(long contract_id)
    {
      String sql =
          "SELECT SUM(no_of_bags)     AS delivered_bags, " +
          "       SUM(delivered_tons) AS delivered_tons " +
          "FROM " + getInst_Table() + " " +
          "WHERE contract_id = " + contract_id ;
      Record rc = new Record(getSQLCommand().executeQuery(sql));
      sql =
          "UPDATE contracts " +
          "SET receive_tons = " +  rc.getFloatValue("delivered_tons") + " " +
          "WHERE id = " + contract_id;
      getSQLCommand().executeUpdate(sql);
      rc.close();
    }

   public long doTask()
   {
     id = super.doTask();
     if (isUpdated()) {
       Table tab = new Table(this,getWN_Table());
       long inst_id = tab.getRecord(id).getLongValue("inst_id");
       update_Inst(inst_id);
       String sql = "SELECT * FROM " + getInst_Table() + " WHERE id = " + inst_id;
       Record rc = new Record(getSQLCommand().executeQuery(sql));
       long contract_id = rc.getLongValue("contract_id");
       update_Contract(contract_id);
       rc.close();
     }
     return id;
   }
}
